#!/usr/bin/env node

/**
 * AIDE 数据库初始化脚本
 * 用于首次部署时初始化数据库表结构
 */

const { Pool } = require('pg')
const fs = require('fs')
const path = require('path')
const { createDatabase } = require('./create-database')

// 数据库配置
const getDatabaseConfig = () => {
  if (process.env.DATABASE_URL) {
    return {
      connectionString: process.env.DATABASE_URL,
      ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false
    }
  }
  
  return {
    host: process.env.DB_HOST || 'localhost',
    port: parseInt(process.env.DB_PORT || '5432'),
    database: process.env.DB_NAME || 'aide_db',
    user: process.env.DB_USER || 'postgres',
    password: process.env.DB_PASSWORD || 'password',
  }
}

async function initializeDatabase() {
  try {
    console.log('🚀 开始初始化 AIDE 数据库...')
    
    // 首先确保数据库存在
    console.log('1️⃣ 检查并创建数据库...')
    const dbCreated = await createDatabase()
    if (!dbCreated) {
      throw new Error('数据库创建失败')
    }
    
    // 连接到目标数据库
    const pool = new Pool(getDatabaseConfig())
    const client = await pool.connect()
    console.log('✅ 数据库连接成功')
    
    // 读取初始化SQL文件
    const sqlPath = path.join(__dirname, 'src', 'database', 'init.sql')
    if (!fs.existsSync(sqlPath)) {
      throw new Error(`初始化SQL文件不存在: ${sqlPath}`)
    }
    
    const initSQL = fs.readFileSync(sqlPath, 'utf8')
    console.log('📖 读取初始化SQL脚本成功')
    
    // 执行初始化SQL
    console.log('🔧 执行数据库初始化...')
    await client.query(initSQL)
    console.log('✅ 数据库表结构创建成功')
    
    // 检查表是否创建成功
    const tablesResult = await client.query(`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' 
      ORDER BY table_name;
    `)
    
    const tables = tablesResult.rows.map(row => row.table_name)
    console.log('📋 已创建的表:')
    tables.forEach(table => {
      console.log(`   - ${table}`)
    })
    
    // 检查索引
    const indexesResult = await client.query(`
      SELECT indexname 
      FROM pg_indexes 
      WHERE schemaname = 'public' 
      AND indexname LIKE 'idx_%'
      ORDER BY indexname;
    `)
    
    const indexes = indexesResult.rows.map(row => row.indexname)
    console.log(`📊 已创建 ${indexes.length} 个索引`)
    
    // 检查触发器
    const triggersResult = await client.query(`
      SELECT trigger_name 
      FROM information_schema.triggers 
      WHERE trigger_schema = 'public'
      ORDER BY trigger_name;
    `)
    
    const triggers = triggersResult.rows.map(row => row.trigger_name)
    console.log(`⚡ 已创建 ${triggers.length} 个触发器`)
    
    // 检查版本信息
    const versionResult = await client.query('SELECT version, applied_at FROM schema_versions ORDER BY applied_at DESC LIMIT 1')
    if (versionResult.rows.length > 0) {
      const { version, applied_at } = versionResult.rows[0]
      console.log(`📌 数据库版本: ${version} (${applied_at.toISOString()})`)
    }
    
    client.release()
    await pool.end()
    console.log('🎉 数据库初始化完成!')
    
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error.message)
    console.error('详细错误:', error)
    process.exit(1)
  }
}

// 创建默认看板列的函数
async function createDefaultKanbanColumns(userId) {
  const pool = new Pool(getDatabaseConfig())
  
  try {
    const client = await pool.connect()
    
    const defaultColumns = [
      { status: 'todo', title: '待处理', color: '#6B7280', order: 0 },
      { status: 'in_progress', title: '进行中', color: '#3B82F6', order: 1 },
      { status: 'review', title: '待审核', color: '#F59E0B', order: 2 },
      { status: 'done', title: '已完成', color: '#10B981', order: 3 }
    ]
    
    for (const column of defaultColumns) {
      await client.query(`
        INSERT INTO kanban_columns (user_id, status, title, color, column_order, is_visible)
        VALUES ($1, $2, $3, $4, $5, $6)
        ON CONFLICT (user_id, status) DO NOTHING
      `, [userId, column.status, column.title, column.color, column.order, true])
    }
    
    client.release()
    console.log(`✅ 为用户 ${userId} 创建默认看板列`)
    
  } catch (error) {
    console.error('❌ 创建默认看板列失败:', error.message)
  } finally {
    await pool.end()
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  // 检查环境变量
  if (!process.env.DATABASE_URL && !process.env.DB_NAME) {
    console.log('⚠️  请设置数据库环境变量:')
    console.log('   方式1: DATABASE_URL=postgresql://user:password@host:port/database')
    console.log('   方式2: DB_HOST, DB_PORT, DB_NAME, DB_USER, DB_PASSWORD')
    console.log('')
    console.log('示例:')
    console.log('   export DATABASE_URL="postgresql://postgres:password@localhost:5432/aide_db"')
    console.log('   node init-database.js')
    process.exit(1)
  }
  
  initializeDatabase()
}

module.exports = {
  initializeDatabase,
  createDefaultKanbanColumns
}
